﻿Imports System.Data.OleDb
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports AjaxControlToolkit


Public Class Cars_Details
    Inherits System.Web.UI.Page


    Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim con As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)
        If Not IsPostBack Then
            BindAverageRatingControl()

            BindRepeaterData()

        End If
    End Sub


    Public Sub BindAverageRatingControl()
        Dim con As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)
        Dim carsCatId = Request.Params("ID")


        Dim RATING As Single = 0

        Dim MyCurrentRating As Single = 0

        Dim dt As DataTable = New DataTable()
        con.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT CarRating.Car_FKID, AVG(Rating) AS AverageRating FROM(CarRating) WHERE Car_FKID=" & carsCatId & "  GROUP BY Car_FKID", con)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        da.Fill(dt)


        If dt.Rows.Count <> 0 Then
            'COUNT = Single.Parse(dt.Rows(0)("Car_FKID").ToString())
            RATING = Single.Parse(dt.Rows(0)("AverageRating").ToString())
            RATING = CType(Math.Round((RATING), 1), Single)
            Rating2.CurrentRating = Convert.ToInt32(RATING)
        Else
            Rating2.CurrentRating = 0
        End If
        lblMyRating.Text = RATING.ToString()

    End Sub

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSubmit.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)
        Dim sql = "Insert into Vehicle_Comments (VehCom_Author,VehCom_Title,VehCom_Mesg,PostedDate,FK_ID) values (@Author, @Title, @Message,@postedDate ,@ID)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@Author", User.Identity.Name)
        cmd.Parameters.AddWithValue("@Title", txtTitle.Text)
        cmd.Parameters.AddWithValue("@Message", txtMessage.Text)
        cmd.Parameters.AddWithValue("@postedDate", DateTime.Now)
        cmd.Parameters.AddWithValue("@ID", Request.Params("ID"))
        cmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect("Car_Details.aspx?ID=" & Request.Params("ID"))

    End Sub

    Protected Sub BindRepeaterData()
        Dim fkID = Request.Params("ID")
        Dim con As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)
        Dim cmd As New OleDbCommand("SELECT [VehCom_Author], [VehCom_Title], [VehCom_Mesg],[PostedDate] FROM [Vehicle_Comments]  WHERE [FK_ID] =" & fkID & "", con)
        Dim ds As New DataSet()
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(ds)
        If ds.Tables(0).Rows.Count > 0 Then
            RepDetails.Visible = True
            RepDetails.DataSource = ds
            RepDetails.DataBind()
        Else
            RepDetails.Visible = False
        End If
        con.Close()
    End Sub

    Protected Sub btnTag_Click(sender As Object, e As EventArgs) Handles btnTag.Click
        Dim carsCatId = Request.Params("ID")
        Session("CatID") = carsCatId
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)
        conn.Open()
        Dim newTags = txtCarTags.Text.Split(",")
        For Each newTag As String In newTags
            Dim isTagPresentCmd As New OleDbCommand("SELECT * FROM Tagging WHERE Tags=@f1", conn)
            isTagPresentCmd.Parameters.AddWithValue("@f1", newTag)
            Dim dr = isTagPresentCmd.ExecuteReader()
            If Not dr.HasRows Then
                Dim addTagCmd As New OleDbCommand("INSERT INTO Tagging (Tags) VALUES (@f1)", conn)
                addTagCmd.Parameters.AddWithValue("@f1", newTag)
                addTagCmd.ExecuteNonQuery()
            End If
            Dim findNewTagIDCmd As New OleDbCommand("SELECT ID FROM Tagging WHERE Tags=@f1", conn)
            findNewTagIDCmd.Parameters.AddWithValue("@f1", newTag)
            Dim newTagDr = findNewTagIDCmd.ExecuteReader()
            newTagDr.Read()
            Dim newID = newTagDr.Item("ID")
            Dim makeAssociationCmd As New OleDbCommand("INSERT INTO CatTagJunction (tFKID,cFKID) VALUES (@f1,@f2)", conn)
            makeAssociationCmd.Parameters.AddWithValue("@f1", newID)
            makeAssociationCmd.Parameters.AddWithValue("@f2", carsCatId)
            makeAssociationCmd.ExecuteNonQuery()
        Next
        For Each li As ListItem In CheckBoxList1.Items
            If (li.Selected = True) Then

                Dim sql = "INSERT INTO CatTagJunction (tFKID,cFKID) VALUES (@f1,@f2)"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@f1", li.Value)

                cmd.Parameters.AddWithValue("@f2", carsCatId)
                cmd.ExecuteNonQuery()
            End If
        Next
        conn.Close()
        Response.Redirect(Request.Url.AbsoluteUri)
    End Sub



    Public Sub RatingControlChanged(ByVal sender As Object, ByVal e As AjaxControlToolkit.RatingEventArgs)
        Dim carsCatId = Request.Params("ID")

        Dim conn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("carsconn").ConnectionString)

        Dim SqlString As String


        Dim rmSql = "delete from CarRating where Rater=@f1 and Car_FKID=@f2"
        Dim rmCmd As New OleDbCommand(rmSql, conn)
        conn.Open()
        rmCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        rmCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        rmCmd.ExecuteNonQuery()
        conn.Close()

        SqlString = "insert into CarRating(Rating,Car_FKID,Rater) values (@f1,@f2,@f3)"
        Dim addCmd As New OleDbCommand(SqlString, conn)
        conn.Open()
        addCmd.Parameters.AddWithValue("@f1", Rating1.CurrentRating)
        addCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        addCmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        addCmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect(Request.Url.AbsoluteUri)
    End Sub



End Class